package Q1;

import Q1.pojo.Student;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.*;

@SuppressWarnings("all")
public class StudentExercise {

    /**
     * A connection (session) with a specific database
     */
    final Connection conn;

    /**
     * 构造函数，传入数据库连接
     *
     * @param conn Connection
     */
    public StudentExercise(Connection conn) {
        this.conn = conn;
    }

    /**
     * 用给定的 no，name，age，birthday 插入到表中
     *
     * @param no       学号
     * @param name     姓名
     * @param age      年龄
     * @param birthday 出生日期
     * @throws SQLException SQLException
     */
    public void insert(String no, String name, int age, Date birthday) throws SQLException {
        try (final PreparedStatement pstmt = conn.prepareStatement("insert into student(no, name, age, birthday) values(?, ?, ?, ?)")) {
            pstmt.setString(1, no);
            pstmt.setString(2, name);
            pstmt.setInt(3, age);
            pstmt.setDate(4, birthday);
            pstmt.executeUpdate();
        }
    }

    /**
     * 删除指定 id 的记录
     *
     * @param id id
     * @throws SQLException SQLException
     */
    public void delete(int id) throws SQLException {
        try (final PreparedStatement pstmt = conn.prepareStatement("delete from student where id=?")) {
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
        }
    }

    /**
     * 修改表数据，按 id 修改数据，类似 insert
     *
     * @param no       学号
     * @param name     姓名
     * @param age      年龄
     * @param birthday 出生日期
     * @throws SQLException SQLException
     */
    public void update(int id, String no, String name, int age, Date birthday) throws SQLException {
        try (final PreparedStatement pstmt = conn.prepareStatement("update student set no=?, name=?, age=?, birthday=? where id=?")) {
            pstmt.setString(1, no);
            pstmt.setString(2, name);
            pstmt.setInt(3, age);
            pstmt.setDate(4, birthday);
            pstmt.setInt(5, id);
            pstmt.executeUpdate();
        }
    }

    /**
     * 按 id 查询学生信息，把查到的数据保存到 Student 对象中
     * 参考：StudentDAO 的 createStudent 方法
     *
     * @param id id
     * @return Student
     * @throws SQLException SQLException
     */
    public Student findById(int id) throws SQLException {
        try (final PreparedStatement pstmt = conn.prepareStatement("select no, name, age, birthday, photo from student where id=?")) {
            pstmt.setInt(1, id);
            try (final ResultSet rs = pstmt.executeQuery()) {
                while (!rs.next()) {
                    return Student.EMPTY_STUDENT;
                }

                return new Student(
                        id,
                        rs.getString("no"),
                        rs.getString("name"),
                        rs.getInt("age"),
                        rs.getDate("birthday"),
                        rs.getBlob("photo")
                );
            }
        }
    }

    /**
     * 插入数据时同时插入图片，这个不好编写测试，各位同学完成后，可以使用
     *
     * @param no       学号
     * @param name     姓名
     * @param age      年龄
     * @param birthday 出生日期
     * @param photo    图片
     * @throws SQLException SQLException
     */
    public void insertPhoto(String no, String name, int age, Date birthday, File photo) throws SQLException {
        try (final PreparedStatement pstmt = conn.prepareStatement("insert into student(no, name, age, birthday, photo) values(?, ?, ?, ?, ?)")) {
            pstmt.setString(1, no);
            pstmt.setString(2, name);
            pstmt.setInt(3, age);
            pstmt.setDate(4, birthday);
            pstmt.setBlob(5, new FileInputStream(photo));
            pstmt.executeUpdate();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

}
